using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;

using Microsoft.ApplicationBlocks.Data;

using DotNetNuke.Common.Utilities;
using DotNetNuke.Framework.Providers;
using CodexOmegaClassLibrary.Constants;

namespace CodexOmega.Modules.VideoLinker
{
    public class SqlDataProvider : DataProvider
    {

        #region Private Members

        private const string ProviderType = "data";
        private const string ModuleQualifier = "CodexOmega_";

        private ProviderConfiguration _providerConfiguration = ProviderConfiguration.GetProviderConfiguration(ProviderType);
        private string _connectionString;
        private string _providerPath;
        private string _objectQualifier;
        private string _databaseOwner;

        #endregion

        #region Constructors

        /// <summary>
        /// Constructs new SqlDataProvider instance
        /// </summary>
        public SqlDataProvider()
        {
            //Read the configuration specific information for this provider
            Provider objProvider = (Provider)_providerConfiguration.Providers[_providerConfiguration.DefaultProvider];

            //Read the attributes for this provider
            //Get Connection string from web.config
            _connectionString = Config.GetConnectionString();

            if (_connectionString.Length == 0)
            {
                //Use connection string specified in provider
                _connectionString = objProvider.Attributes["connectionString"];
            }

            _providerPath = objProvider.Attributes["providerPath"];

            _objectQualifier = objProvider.Attributes["objectQualifier"];

            if ((_objectQualifier != "") && (_objectQualifier.EndsWith("_") == false))
            {
                _objectQualifier += "_";
            }

            _databaseOwner = objProvider.Attributes["databaseOwner"];
            if ((_databaseOwner != "") && (_databaseOwner.EndsWith(".") == false))
            {
                _databaseOwner += ".";
            }
        }

        #endregion

        #region Properties

        /// <summary>
        /// Gets and sets the connection string
        /// </summary>
        public string ConnectionString
        {
            get { return _connectionString; }
        }

        /// <summary>
        /// Gets and sets the Provider path
        /// </summary>
        public string ProviderPath
        {
            get { return _providerPath; }
        }

        /// <summary>
        /// Gets and sets the Object qualifier
        /// </summary>
        public string ObjectQualifier
        {
            get { return _objectQualifier; }
        }

        /// <summary>
        /// Gets and sets the database ownere
        /// </summary>
        public string DatabaseOwner
        {
            get { return _databaseOwner; }
        }

        #endregion

        #region Private Methods

        /// -----------------------------------------------------------------------------
        /// <summary>
        /// Gets the fully qualified name of the stored procedure
        /// </summary>
        /// <param name="name">The name of the stored procedure</param>
        /// <returns>The fully qualified name</returns>
        /// -----------------------------------------------------------------------------
        private string GetFullyQualifiedName(string name)
        {
            return DatabaseOwner + ObjectQualifier + ModuleQualifier + name;
        }

        /// -----------------------------------------------------------------------------
        /// <summary>
        /// Gets the value for the field or DbNull if field has "null" value
        /// </summary>
        /// <param name="Field">The field to evaluate</param>
        /// <returns></returns>
        /// -----------------------------------------------------------------------------
        private Object GetNull(Object Field)
        {
            return Null.GetNull(Field, DBNull.Value);
        }

        #endregion

        #region Public Methods

        public override int CreateVideoLink(VideoLinkerInfo videoLinkerInfo)
        {
            int videoID = -1;
            SqlConnection connection = null;
            SqlCommand command = null;
            SqlTransaction transaction = null;

            try
            {
                // open connection
                connection = new SqlConnection(this.ConnectionString);
                connection.Open();
                transaction = connection.BeginTransaction();

                // init command
                command = new SqlCommand();
                command.Connection = connection;
                command.Transaction = transaction;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = DBConstants.VideoLinkerQueries.CreateVideoLink;

                // set command parameters
                command.Parameters.AddWithValue("@" + DBConstants.VideoLinkerFields.TITLE, videoLinkerInfo.VideoTitle);
                command.Parameters.AddWithValue("@" + DBConstants.VideoLinkerFields.DESCRIPTION, videoLinkerInfo.VideoDescription);
                command.Parameters.AddWithValue("@" + DBConstants.VideoLinkerFields.CODE, videoLinkerInfo.VideoCode);
                command.Parameters.AddWithValue("@" + DBConstants.VideoLinkerFields.VIDEO_CATEGORY_ID, videoLinkerInfo.VideoCategoryID);
                command.Parameters.AddWithValue("@" + DBConstants.VideoLinkerFields.VIDEO_TYPE_ID, videoLinkerInfo.VideoTypeID);
                command.Parameters.Add("@RETURN_VALUE", SqlDbType.Int);
                command.Parameters["@RETURN_VALUE"].Direction = ParameterDirection.Output;

                // execute command
                command.ExecuteNonQuery();

                // execute command
                videoID = (int)(command.Parameters["@RETURN_VALUE"].Value);
                transaction.Commit();
            }
            catch
            {
                if (transaction != null)
                {
                    transaction.Rollback();
                }

                throw;
            }
            finally
            {
                if (connection != null)
                {
                    // close connection
                    connection.Close();
                }
            }

            // return
            return videoID;
        }

        public override bool DeleteVideoLink(int videoID)
        {
            bool isDeleted = false;
            SqlConnection connection = null;
            SqlCommand command = null;
            SqlTransaction transaction = null;

            try
            {
                // open connection
                connection = new SqlConnection(this.ConnectionString);
                connection.Open();
                transaction = connection.BeginTransaction();

                // init command
                command = new SqlCommand();
                command.Connection = connection;
                command.Transaction = transaction;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = DBConstants.VideoLinkerQueries.DeleteVideoLink;

                // set command parameters
                command.Parameters.AddWithValue("@" + DBConstants.VideoLinkerFields.VIDEO_ID, videoID);

                // execute command
                command.ExecuteNonQuery();
                transaction.Commit();
                isDeleted = true;
            }
            catch
            {
                if (transaction != null)
                {
                    transaction.Rollback();
                }

                throw;
            }
            finally
            {
                if (connection != null)
                {
                    // close connection
                    connection.Close();
                }
            }
            // return
            return isDeleted;
        }

        public override VideoLinkerInfo GetVideoLink(int videoID)
        {
            VideoLinkerInfo videoLinkerInfo = null;
            SqlConnection connection = null;
            SqlCommand command = null;
            IDataReader reader = null;

            try
            {
                // open connection
                connection = new SqlConnection(this.ConnectionString);
                connection.Open();

                // init command
                command = new SqlCommand();
                command.Connection = connection;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = DBConstants.VideoLinkerQueries.GetVideoOnID;

                // set command parameters
                command.Parameters.AddWithValue("@" + DBConstants.VideoLinkerFields.VIDEO_ID, videoID);

                // execute command
                reader = command.ExecuteReader();

                // read
                if (reader.Read())
                {
                    // create new entity
                    videoLinkerInfo = new VideoLinkerInfo();

                    // get values
                    videoLinkerInfo.VideoID = reader[DBConstants.VideoLinkerFields.VIDEO_ID] != DBNull.Value ? Convert.ToInt32(reader[DBConstants.VideoLinkerFields.VIDEO_ID]) : -1;
                    videoLinkerInfo.VideoCategoryID = reader[DBConstants.VideoLinkerFields.VIDEO_CATEGORY_ID] != DBNull.Value ? Convert.ToInt32(reader[DBConstants.VideoLinkerFields.VIDEO_CATEGORY_ID]) : -1;
                    videoLinkerInfo.VideoTypeID = reader[DBConstants.VideoLinkerFields.VIDEO_TYPE_ID] != DBNull.Value ? Convert.ToInt32(reader[DBConstants.VideoLinkerFields.VIDEO_TYPE_ID]) : -1;
                    videoLinkerInfo.VideoTitle = reader[DBConstants.VideoLinkerFields.TITLE] != DBNull.Value ? Convert.ToString(reader[DBConstants.VideoLinkerFields.TITLE]) : string.Empty;
                    videoLinkerInfo.VideoDescription = reader[DBConstants.VideoLinkerFields.DESCRIPTION] != DBNull.Value ? Convert.ToString(reader[DBConstants.VideoLinkerFields.DESCRIPTION]) : string.Empty;
                    videoLinkerInfo.VideoCode = reader[DBConstants.VideoLinkerFields.CODE] != DBNull.Value ? Convert.ToString(reader[DBConstants.VideoLinkerFields.CODE]) : string.Empty;
                }
                // close reader
                reader.Close();
            }
            catch
            {
                throw;
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
            }

            // return
            return videoLinkerInfo;
        }

        public override List<VideoLinkerInfo> GetVideoLinksList(int typeID, int categoryID, string keyword)
        {
            List<VideoLinkerInfo> listVideos = null;
            VideoLinkerInfo videoLinkerInfo = null;
            SqlConnection connection = null;
            SqlCommand command = null;
            IDataReader reader = null;

            try
            {
                // open connection
                connection = new SqlConnection(this.ConnectionString);
                connection.Open();

                // init command
                command = new SqlCommand();
                command.Connection = connection;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = DBConstants.VideoLinkerQueries.GetVideosList;

                // set command parameters
                command.Parameters.AddWithValue("@" + DBConstants.VideoLinkerFields.VIDEO_TYPE_ID, typeID);
                command.Parameters.AddWithValue("@" + DBConstants.VideoLinkerFields.VIDEO_CATEGORY_ID, categoryID);
                command.Parameters.AddWithValue("@" + DBConstants.VideoLinkerFields.KEYWORD, keyword);

                // execute command
                reader = command.ExecuteReader();

                // read
                listVideos = new List<VideoLinkerInfo>();
                while (reader.Read())
                {
                    // create new entity
                    videoLinkerInfo = new VideoLinkerInfo();

                    // get values
                    videoLinkerInfo.VideoID = reader[DBConstants.VideoLinkerFields.VIDEO_ID] != DBNull.Value ? Convert.ToInt32(reader[DBConstants.VideoLinkerFields.VIDEO_ID]) : -1;
                    videoLinkerInfo.VideoTitle = reader[DBConstants.VideoLinkerFields.TITLE] != DBNull.Value ? Convert.ToString(reader[DBConstants.VideoLinkerFields.TITLE]) : string.Empty;
                    videoLinkerInfo.VideoTypeName = reader[DBConstants.VideoLinkerFields.VIDEO_TYPE_NAME] != DBNull.Value ? Convert.ToString(reader[DBConstants.VideoLinkerFields.VIDEO_TYPE_NAME]) : string.Empty;
                    videoLinkerInfo.VideoCategoryName = reader[DBConstants.VideoLinkerFields.VIDEO_CATEGORY_NAME] != DBNull.Value ? Convert.ToString(reader[DBConstants.VideoLinkerFields.VIDEO_CATEGORY_NAME]) : string.Empty;


                    // add new entity to the list
                    listVideos.Add(videoLinkerInfo);
                }

                // close reader
                reader.Close();
            }
            catch
            {
                throw;
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
            }

            // return
            return listVideos;
        }

        public override bool UpdateVideoLink(VideoLinkerInfo videoLinkerInfo)
        {
            bool isUpdated = false;
            SqlConnection connection = null;
            SqlCommand command = null;
            SqlTransaction transaction = null;

            try
            {
                // open connection
                connection = new SqlConnection(this.ConnectionString);
                connection.Open();
                transaction = connection.BeginTransaction();

                // init command
                command = new SqlCommand();
                command.Connection = connection;
                command.Transaction = transaction;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = DBConstants.VideoLinkerQueries.UpdateVideoLink;

                // set command parameters
                command.Parameters.AddWithValue("@" + DBConstants.VideoLinkerFields.VIDEO_ID, videoLinkerInfo.VideoID);
                command.Parameters.AddWithValue("@" + DBConstants.VideoLinkerFields.TITLE, videoLinkerInfo.VideoTitle);
                command.Parameters.AddWithValue("@" + DBConstants.VideoLinkerFields.DESCRIPTION, videoLinkerInfo.VideoDescription);
                command.Parameters.AddWithValue("@" + DBConstants.VideoLinkerFields.CODE, videoLinkerInfo.VideoCode);
                command.Parameters.AddWithValue("@" + DBConstants.VideoLinkerFields.VIDEO_CATEGORY_ID, videoLinkerInfo.VideoCategoryID);
                command.Parameters.AddWithValue("@" + DBConstants.VideoLinkerFields.VIDEO_TYPE_ID, videoLinkerInfo.VideoTypeID);

                // execute command
                command.ExecuteNonQuery();
                transaction.Commit();
                isUpdated = true;
            }
            catch
            {
                if (transaction != null)
                {
                    transaction.Rollback();
                }

                throw;
            }
            finally
            {
                if (connection != null)
                {
                    // close connection
                    connection.Close();
                }
            }
            // return
            return isUpdated;
        }

        public override Dictionary<string, int> GetVideoCategoriesList()
        {
            Dictionary<string, int> listCategories = null;
            SqlConnection connection = null;
            SqlCommand command = null;
            IDataReader reader = null;

            try
            {
                // open connection
                connection = new SqlConnection(this.ConnectionString);
                connection.Open();

                // init command
                command = new SqlCommand();
                command.Connection = connection;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = DBConstants.VideoLinkerQueries.GetCategoriesList;


                // execute command
                reader = command.ExecuteReader();

                // read
                listCategories = new Dictionary<string, int>();
                while (reader.Read())
                {
                    // add new entity to the list
                    listCategories.Add(reader[DBConstants.VideoLinkerFields.DEFINITION].ToString(), Convert.ToInt32(reader[DBConstants.VideoLinkerFields.VIDEO_CATEGORY_ID]));
                }

                // close reader
                reader.Close();
            }
            catch
            {
                throw;
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
            }

            // return
            return listCategories;
        }

        public override Dictionary<string, int> GetVideoTypesList()
        {
            Dictionary<string, int> listTypes = null;
            SqlConnection connection = null;
            SqlCommand command = null;
            IDataReader reader = null;

            try
            {
                // open connection
                connection = new SqlConnection(this.ConnectionString);
                connection.Open();

                // init command
                command = new SqlCommand();
                command.Connection = connection;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = DBConstants.VideoLinkerQueries.GetTypesList;


                // execute command
                reader = command.ExecuteReader();

                // read
                listTypes = new Dictionary<string, int>();
                while (reader.Read())
                {
                    // add new entity to the list
                    listTypes.Add(reader[DBConstants.VideoLinkerFields.DEFINITION].ToString(), Convert.ToInt32(reader[DBConstants.VideoLinkerFields.VIDEO_TYPE_ID]));
                }

                // close reader
                reader.Close();
            }
            catch
            {
                throw;
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
            }

            // return
            return listTypes;
        }

        public override string GetVideoCategory(int categoryID)
        {
            string videoCategory = null;
            SqlConnection connection = null;
            SqlCommand command = null;
            IDataReader reader = null;

            try
            {
                // open connection
                connection = new SqlConnection(this.ConnectionString);
                connection.Open();

                // init command
                command = new SqlCommand();
                command.Connection = connection;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = DBConstants.VideoLinkerQueries.GetCategoryOnID;

                // set command parameters
                command.Parameters.AddWithValue("@" + DBConstants.VideoLinkerFields.VIDEO_CATEGORY_ID, categoryID);

                // execute command
                reader = command.ExecuteReader();

                // read
                if (reader.Read())
                {
                    // get value
                    videoCategory = reader[DBConstants.VideoLinkerFields.VIDEO_CATEGORY_NAME].ToString();
                }
                // close reader
                reader.Close();
            }
            catch
            {
                throw;
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
            }

            // return
            return videoCategory;
        }

        public override string GetVideoType(int typeID)
        {
            string videoType = null;
            SqlConnection connection = null;
            SqlCommand command = null;
            IDataReader reader = null;

            try
            {
                // open connection
                connection = new SqlConnection(this.ConnectionString);
                connection.Open();

                // init command
                command = new SqlCommand();
                command.Connection = connection;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = DBConstants.VideoLinkerQueries.GetTypeOnID;

                // set command parameters
                command.Parameters.AddWithValue("@" + DBConstants.VideoLinkerFields.VIDEO_TYPE_ID, typeID);

                // execute command
                reader = command.ExecuteReader();

                // read
                if (reader.Read())
                {
                    // get value
                    videoType = reader[DBConstants.VideoLinkerFields.VIDEO_TYPE_NAME].ToString();
                }
                // close reader
                reader.Close();
            }
            catch
            {
                throw;
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
            }

            // return
            return videoType;
        }

        public override int CreateVideoCategory(string definition)
        {
            int videoCategoryID = -1;
            SqlConnection connection = null;
            SqlCommand command = null;
            SqlTransaction transaction = null;

            try
            {
                // open connection
                connection = new SqlConnection(this.ConnectionString);
                connection.Open();
                transaction = connection.BeginTransaction();

                // init command
                command = new SqlCommand();
                command.Connection = connection;
                command.Transaction = transaction;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = DBConstants.VideoLinkerQueries.CreateVideoCategory;

                // set command parameters
                command.Parameters.AddWithValue("@" + DBConstants.VideoLinkerFields.DEFINITION, definition);
                command.Parameters.Add("@RETURN_VALUE", SqlDbType.Int);
                command.Parameters["@RETURN_VALUE"].Direction = ParameterDirection.Output;

                // execute command
                command.ExecuteNonQuery();

                // execute command
                videoCategoryID = (int)(command.Parameters["@RETURN_VALUE"].Value);
                transaction.Commit();
            }
            catch
            {
                if (transaction != null)
                {
                    transaction.Rollback();
                }

                throw;
            }
            finally
            {
                if (connection != null)
                {
                    // close connection
                    connection.Close();
                }
            }

            // return
            return videoCategoryID;
        }

        public override int CreateVideoType(string definition)
        {
            int videoTypeID = -1;
            SqlConnection connection = null;
            SqlCommand command = null;
            SqlTransaction transaction = null;


            try
            {
                // open connection
                connection = new SqlConnection(this.ConnectionString);
                connection.Open();
                transaction = connection.BeginTransaction();

                // init command
                command = new SqlCommand();
                command.Connection = connection;
                command.Transaction = transaction;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = DBConstants.VideoLinkerQueries.CreateVideoType;

                // set command parameters
                command.Parameters.AddWithValue("@" + DBConstants.VideoLinkerFields.DEFINITION, definition);
                command.Parameters.Add("@RETURN_VALUE", SqlDbType.Int);
                command.Parameters["@RETURN_VALUE"].Direction = ParameterDirection.Output;

                // execute command
                command.ExecuteNonQuery();

                // execute command
                videoTypeID = (int)(command.Parameters["@RETURN_VALUE"].Value);

                // commit transaction
                transaction.Commit();
            }
            catch
            {
                if (transaction != null)
                {
                    transaction.Rollback();
                }

                throw;
            }
            finally
            {
                if (connection != null)
                {
                    // close connection
                    connection.Close();
                }
            }

            // return
            return videoTypeID;
        }

        public override bool UpdateVideoCategory(int categoryID, string definition)
        {
            bool isUpdated = false;
            SqlConnection connection = null;
            SqlCommand command = null;
            SqlTransaction transaction = null;

            try
            {
                // open connection
                connection = new SqlConnection(this.ConnectionString);
                connection.Open();
                transaction = connection.BeginTransaction();

                // init command
                command = new SqlCommand();
                command.Connection = connection;
                command.Transaction = transaction;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = DBConstants.VideoLinkerQueries.UpdateVideoCategory;

                // set command parameters
                command.Parameters.AddWithValue("@" + DBConstants.VideoLinkerFields.VIDEO_CATEGORY_ID, categoryID);
                command.Parameters.AddWithValue("@" + DBConstants.VideoLinkerFields.DEFINITION, definition);

                // execute command
                command.ExecuteNonQuery();
                transaction.Commit();
                isUpdated = true;
            }
            catch
            {
                if (transaction != null)
                {
                    transaction.Rollback();
                }

                throw;
            }
            finally
            {
                if (connection != null)
                {
                    // close connection
                    connection.Close();
                }
            }
            // return
            return isUpdated;
        }

        public override bool UpdateVideoType(int typeID, string definition)
        {
            bool isUpdated = false;
            SqlConnection connection = null;
            SqlCommand command = null;
            SqlTransaction transaction = null;

            try
            {
                // open connection
                connection = new SqlConnection(this.ConnectionString);
                connection.Open();
                transaction = connection.BeginTransaction();

                // init command
                command = new SqlCommand();
                command.Connection = connection;
                command.Transaction = transaction;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = DBConstants.VideoLinkerQueries.UpdateVideoType;

                // set command parameters
                command.Parameters.AddWithValue("@" + DBConstants.VideoLinkerFields.VIDEO_TYPE_ID, typeID);
                command.Parameters.AddWithValue("@" + DBConstants.VideoLinkerFields.DEFINITION, definition);

                // execute command
                command.ExecuteNonQuery();
                transaction.Commit();
                isUpdated = true;
            }
            catch
            {
                if (transaction != null)
                {
                    transaction.Rollback();
                }

                throw;
            }
            finally
            {
                if (connection != null)
                {
                    // close connection
                    connection.Close();
                }
            }
            // return
            return isUpdated;
        }

        public override bool DeleteVideoCategory(int categoryID)
        {
            bool isDeleted = false;
            SqlConnection connection = null;
            SqlCommand command = null;
            SqlTransaction transaction = null;

            try
            {
                // open connection
                connection = new SqlConnection(this.ConnectionString);
                connection.Open();
                transaction = connection.BeginTransaction();

                // init command
                command = new SqlCommand();
                command.Connection = connection;
                command.Transaction = transaction;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = DBConstants.VideoLinkerQueries.DeleteVideoCategory;

                // set command parameters
                command.Parameters.AddWithValue("@" + DBConstants.VideoLinkerFields.VIDEO_CATEGORY_ID, categoryID);

                // execute command
                command.ExecuteNonQuery();
                transaction.Commit();
                isDeleted = true;
            }
            catch
            {
                if (transaction != null)
                {
                    transaction.Rollback();
                }

                throw;
            }
            finally
            {
                if (connection != null)
                {
                    // close connection
                    connection.Close();
                }
            }
            // return
            return isDeleted;
        }

        public override bool DeleteVideoType(int typeID)
        {
            bool isDeleted = false;
            SqlConnection connection = null;
            SqlCommand command = null;
            SqlTransaction transaction = null;

            try
            {
                // open connection
                connection = new SqlConnection(this.ConnectionString);
                connection.Open();
                transaction = connection.BeginTransaction();

                // init command
                command = new SqlCommand();
                command.Connection = connection;
                command.Transaction = transaction;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = DBConstants.VideoLinkerQueries.DeleteVideoType;

                // set command parameters
                command.Parameters.AddWithValue("@" + DBConstants.VideoLinkerFields.VIDEO_TYPE_ID, typeID);

                // execute command
                command.ExecuteNonQuery();
                transaction.Commit();
                isDeleted = true;
            }
            catch
            {
                if (transaction != null)
                {
                    transaction.Rollback();
                }

                throw;
            }
            finally
            {
                if (connection != null)
                {
                    // close connection
                    connection.Close();
                }
            }
            // return
            return isDeleted;
        }


        #endregion

    }
}
